import sqlite3
import os
import json
from datetime import datetime
from typing import Dict, List, Optional, Any

class DatabaseManager:
    def __init__(self, db_path: str = "wx_accounts.db"):
        """初始化数据库管理器
        
        Args:
            db_path: 数据库文件路径
        """
        self.db_path = db_path
        self.init_database()
    
    def init_database(self):
        """初始化数据库，创建必要的表"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 创建用户账号表
                cursor.execute('''
                    CREATE TABLE IF NOT EXISTS user_accounts (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        user_name TEXT NOT NULL UNIQUE,
                        nick_name TEXT,
                        alias TEXT,
                        bind_mobile TEXT,
                        status INTEGER DEFAULT 0,
                        login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        remark TEXT
                    )
                ''')
                
                # 创建登录日志表
                cursor.execute('''
                    CREATE TABLE IF NOT EXISTS login_logs (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        user_name TEXT NOT NULL,
                        login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        login_status TEXT,
                        proxy_name TEXT,
                        remark TEXT
                    )
                ''')
                
                # 创建邀请任务表
                cursor.execute('''
                    CREATE TABLE IF NOT EXISTS invite_tasks (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        task_id TEXT NOT NULL UNIQUE,
                        task_name TEXT NOT NULL,
                        created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        status TEXT DEFAULT '进行中',
                        progress INTEGER DEFAULT 0,
                        total_accounts INTEGER DEFAULT 0,
                        completed_accounts INTEGER DEFAULT 0,
                        remark TEXT
                    )
                ''')
                
                # 创建邀请明细表
                cursor.execute('''
                    CREATE TABLE IF NOT EXISTS invite_details (
                        invite_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        user_name TEXT NOT NULL,
                        group_name TEXT NOT NULL,
                        invite_url TEXT,  -- 添加邀请链接字段
                        inviter_name TEXT,  -- 邀请人昵称
                        invite_result TEXT DEFAULT '等待中',
                        remark TEXT,
                        status TEXT DEFAULT '未处理',
                        task_id TEXT,
                        join_time TIMESTAMP,  -- 进群时间
                        PRIMARY KEY (user_name, group_name),  -- 复合主键
                        FOREIGN KEY (task_id) REFERENCES invite_tasks (task_id)
                    )
                ''')
                
                # 创建模板表
                cursor.execute('''
                    CREATE TABLE IF NOT EXISTS templates (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        name TEXT NOT NULL,
                        description TEXT,
                        create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    )
                ''')
                
                # 创建对话表
                cursor.execute('''
                    CREATE TABLE IF NOT EXISTS template_dialogues (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        template_id INTEGER NOT NULL,
                        speaker TEXT NOT NULL,  -- 'A' 或 'B'
                        message_type TEXT NOT NULL,  -- 'text', 'voice', 'image', 'link'
                        content TEXT NOT NULL,
                        use_groups BOOLEAN DEFAULT 0,  -- 是否使用分组
                        group_data TEXT,  -- JSON格式存储分组数据
                        order_index INTEGER NOT NULL,
                        create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        FOREIGN KEY (template_id) REFERENCES templates (id) ON DELETE CASCADE
                    )
                ''')
                
                # 创建群聊表
                cursor.execute('''
                    CREATE TABLE IF NOT EXISTS user_groups (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        user_name TEXT NOT NULL,
                        qid TEXT NOT NULL,  -- 群聊ID
                        group_name TEXT NOT NULL,
                        inviter_name TEXT,  -- 邀请人昵称
                        invite_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        join_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        status TEXT DEFAULT 'active',  -- active, left, removed
                        remark TEXT,
                        FOREIGN KEY (user_name) REFERENCES user_accounts (user_name)
                    )
                ''')

                # 创建代理配置表
                cursor.execute('''
                    CREATE TABLE IF NOT EXISTS proxy_settings (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        name TEXT NOT NULL UNIQUE,
                        ProxyIp TEXT NOT NULL,
                        ProxyUser TEXT,
                        ProxyPassword TEXT,
                        created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    )
                ''')
                
                conn.commit()
                print("数据库初始化完成")
                
                # 执行数据库迁移
                self._migrate_database(cursor)
                conn.commit()
                
        except Exception as e:
            print(f"数据库初始化失败: {e}")
    
    def _migrate_database(self, cursor):
        """数据库迁移：检查并添加缺失的列"""
        try:
            # 检查invite_details表是否存在invite_url列
            cursor.execute("PRAGMA table_info(invite_details)")
            columns = [column[1] for column in cursor.fetchall()]
            
            if 'invite_url' not in columns:
                print("正在添加invite_url列到invite_details表...")
                cursor.execute('''
                    ALTER TABLE invite_details 
                    ADD COLUMN invite_url TEXT
                ''')
                print("✅ invite_url列添加成功")
            
            # 检查inviter_name列
            if 'inviter_name' not in columns:
                print("正在添加inviter_name列到invite_details表...")
                cursor.execute('''
                    ALTER TABLE invite_details 
                    ADD COLUMN inviter_name TEXT
                ''')
                print("✅ inviter_name列添加成功")
            
            # 检查join_time列
            if 'join_time' not in columns:
                print("正在添加join_time列到invite_details表...")
                cursor.execute('''
                    ALTER TABLE invite_details 
                    ADD COLUMN join_time TIMESTAMP
                ''')
                print("✅ join_time列添加成功")
            
            # 检查其他可能需要迁移的列
            if 'status' not in columns:
                print("正在添加status列到invite_details表...")
                cursor.execute('''
                    ALTER TABLE invite_details 
                    ADD COLUMN status TEXT DEFAULT '未处理'
                ''')
                print("✅ status列添加成功")
            
            # 检查是否需要添加复合主键约束
            try:
                # 检查是否已经有复合主键约束
                cursor.execute("PRAGMA index_list(invite_details)")
                indexes = cursor.fetchall()
                has_composite_pk = any('sqlite_autoindex' in str(index) for index in indexes)
                
                if not has_composite_pk:
                    print("正在添加复合主键约束到invite_details表...")
                    # 创建临时表
                    cursor.execute('''
                        CREATE TABLE invite_details_new (
                            invite_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                            user_name TEXT NOT NULL,
                            group_name TEXT NOT NULL,
                            invite_url TEXT,
                            inviter_name TEXT,
                            invite_result TEXT DEFAULT '等待中',
                            remark TEXT,
                            status TEXT DEFAULT '未处理',
                            task_id TEXT,
                            join_time TIMESTAMP,
                            PRIMARY KEY (user_name, group_name),
                            FOREIGN KEY (task_id) REFERENCES invite_tasks (task_id)
                        )
                    ''')
                    
                    # 复制数据
                    cursor.execute('''
                        INSERT INTO invite_details_new 
                        SELECT invite_time, user_name, group_name, invite_url, invite_result, remark, status, task_id
                        FROM invite_details
                    ''')
                    
                    # 删除旧表
                    cursor.execute('DROP TABLE invite_details')
                    
                    # 重命名新表
                    cursor.execute('ALTER TABLE invite_details_new RENAME TO invite_details')
                    
                    print("✅ 复合主键约束添加成功")
            except Exception as e:
                print(f"⚠️ 添加复合主键约束失败: {e}")
            
            print("数据库迁移完成")
            
        except Exception as e:
            print(f"数据库迁移失败: {e}")
            # 迁移失败不应该阻止程序运行
    
    def insert_user_account(self, user_info: Dict[str, Any]) -> bool:
        """插入或更新用户账号信息
        
        Args:
            user_info: 用户信息字典，包含userName, nickName, alias, bindMobile等
            
        Returns:
            bool: 操作是否成功
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 检查用户是否已存在
                cursor.execute(
                    "SELECT id FROM user_accounts WHERE user_name = ?",
                    (user_info.get('userName'),)
                )
                existing_user = cursor.fetchone()
                
                if existing_user:
                    # 更新现有用户信息
                    cursor.execute('''
                        UPDATE user_accounts 
                        SET nick_name = ?, alias = ?, bind_mobile = ?, 
                            status = ?, update_time = CURRENT_TIMESTAMP
                        WHERE user_name = ?
                    ''', (
                        user_info.get('nickName', ''),
                        user_info.get('alias', ''),
                        user_info.get('bindMobile', ''),
                        user_info.get('status', 0),
                        user_info.get('userName')
                    ))
                    print(f"更新用户信息: {user_info.get('userName')}")
                else:
                    # 插入新用户
                    cursor.execute('''
                        INSERT INTO user_accounts 
                        (user_name, nick_name, alias, bind_mobile, status, remark)
                        VALUES (?, ?, ?, ?, ?, ?)
                    ''', (
                        user_info.get('userName', ''),
                        user_info.get('nickName', ''),
                        user_info.get('alias', ''),
                        user_info.get('bindMobile', ''),
                        user_info.get('status', 0),
                        user_info.get('remark', '')
                    ))
                    print(f"插入新用户: {user_info.get('userName')}")
                
                # 记录登录日志
                cursor.execute('''
                    INSERT INTO login_logs 
                    (user_name, login_status, proxy_name, remark)
                    VALUES (?, ?, ?, ?)
                ''', (
                    user_info.get('userName', ''),
                    'success',
                    user_info.get('proxy_name', ''),
                    f"登录成功 - {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}"
                ))
                
                conn.commit()
                return True
                
        except Exception as e:
            print(f"插入用户账号失败: {e}")
            return False
    
    def get_all_accounts(self) -> List[Dict[str, Any]]:
        """获取所有账号信息
        
        Returns:
            List[Dict]: 账号信息列表
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT id, user_name, nick_name, alias, bind_mobile, 
                           status, login_time, update_time, remark
                    FROM user_accounts 
                    ORDER BY update_time DESC
                ''')
                
                columns = [description[0] for description in cursor.description]
                accounts = []
                
                for row in cursor.fetchall():
                    account = dict(zip(columns, row))
                    accounts.append(account)
                
                return accounts
                
        except Exception as e:
            print(f"获取账号列表失败: {e}")
            return []
    
    def get_online_accounts(self) -> List[Dict[str, Any]]:
        """获取在线账号信息（status = 1表示在线）
        
        Returns:
            List[Dict]: 在线账号信息列表
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT id, user_name, nick_name, alias, bind_mobile, 
                           status, login_time, update_time, remark
                    FROM user_accounts 
                    WHERE status = 1
                    ORDER BY update_time DESC
                ''')
                
                columns = [description[0] for description in cursor.description]
                accounts = []
                
                for row in cursor.fetchall():
                    account = dict(zip(columns, row))
                    accounts.append(account)
                
                return accounts
                
        except Exception as e:
            print(f"获取在线账号列表失败: {e}")
            return []
    
    def get_account_by_username(self, user_name: str) -> Optional[Dict[str, Any]]:
        """根据用户名获取账号信息
        
        Args:
            user_name: 用户名
            
        Returns:
            Optional[Dict]: 账号信息，如果不存在返回None
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT id, user_name, nick_name, alias, bind_mobile, 
                           status, login_time, update_time, remark
                    FROM user_accounts 
                    WHERE user_name = ?
                ''', (user_name,))
                
                row = cursor.fetchone()
                if row:
                    columns = [description[0] for description in cursor.description]
                    return dict(zip(columns, row))
                return None
                
        except Exception as e:
            print(f"获取账号信息失败: {e}")
            return None
    
    def update_account_status(self, user_name: str, status: int) -> bool:
        """更新账号状态
        
        Args:
            user_name: 用户名
            status: 状态值
            
        Returns:
            bool: 操作是否成功
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    UPDATE user_accounts 
                    SET status = ?, update_time = CURRENT_TIMESTAMP
                    WHERE user_name = ?
                ''', (status, user_name))
                
                conn.commit()
                return cursor.rowcount > 0
                
        except Exception as e:
            print(f"更新账号状态失败: {e}")
            return False
    
    def delete_account(self, user_name: str) -> bool:
        """删除账号
        
        Args:
            user_name: 用户名
            
        Returns:
            bool: 操作是否成功
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('DELETE FROM user_accounts WHERE user_name = ?', (user_name,))
                conn.commit()
                return cursor.rowcount > 0
                
        except Exception as e:
            print(f"删除账号失败: {e}")
            return False
    
    def get_login_logs(self, limit: int = 50) -> List[Dict[str, Any]]:
        """获取登录日志
        
        Args:
            limit: 限制返回的记录数
            
        Returns:
            List[Dict]: 登录日志列表
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT id, user_name, login_time, login_status, proxy_name, remark
                    FROM login_logs 
                    ORDER BY login_time DESC
                    LIMIT ?
                ''', (limit,))
                
                columns = [description[0] for description in cursor.description]
                logs = []
                
                for row in cursor.fetchall():
                    log = dict(zip(columns, row))
                    logs.append(log)
                
                return logs
                
        except Exception as e:
            print(f"获取登录日志失败: {e}")
            return []

    # 代理配置 CRUD
    def save_proxy_settings(self, name: str, proxy_ip: str, proxy_user: str = "", proxy_password: str = "") -> bool:
        """新增或更新代理配置"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute(
                    '''
                    INSERT INTO proxy_settings (name, ProxyIp, ProxyUser, ProxyPassword)
                    VALUES (?, ?, ?, ?)
                    ON CONFLICT(name) DO UPDATE SET
                        ProxyIp=excluded.ProxyIp,
                        ProxyUser=excluded.ProxyUser,
                        ProxyPassword=excluded.ProxyPassword,
                        update_time=CURRENT_TIMESTAMP
                    ''',
                    (name, proxy_ip, proxy_user, proxy_password)
                )
                conn.commit()
                return True
        except Exception as e:
            print(f"保存代理配置失败: {e}")
            return False

    def get_proxy_by_name(self, name: str) -> Optional[Dict[str, Any]]:
        """根据名称获取代理配置"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute(
                    'SELECT name, ProxyIp, ProxyUser, ProxyPassword FROM proxy_settings WHERE name = ?',
                    (name,)
                )
                row = cursor.fetchone()
                if row:
                    return {
                        'name': row[0],
                        'ProxyIp': row[1],
                        'ProxyUser': row[2] or '',
                        'ProxyPassword': row[3] or ''
                    }
                return None
        except Exception as e:
            print(f"获取代理配置失败: {e}")
            return None

    def get_all_proxy_settings(self) -> List[Dict[str, Any]]:
        """获取全部代理配置"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('SELECT name, ProxyIp, ProxyUser, ProxyPassword FROM proxy_settings ORDER BY update_time DESC')
                rows = cursor.fetchall()
                return [
                    {
                        'name': r[0],
                        'ProxyIp': r[1],
                        'ProxyUser': r[2] or '',
                        'ProxyPassword': r[3] or ''
                    }
                    for r in rows
                ]
        except Exception as e:
            print(f"获取全部代理配置失败: {e}")
            return []

    def delete_proxy_settings(self, name: Optional[str] = None) -> bool:
        """删除代理配置；name 为空则清空所有"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                if name:
                    cursor.execute('DELETE FROM proxy_settings WHERE name = ?', (name,))
                else:
                    cursor.execute('DELETE FROM proxy_settings')
                conn.commit()
                return True
        except Exception as e:
            print(f"删除代理配置失败: {e}")
            return False
    
    def get_invite_tasks(self) -> List[Dict[str, Any]]:
        """获取邀请任务列表
        
        Returns:
            List[Dict]: 邀请任务列表
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT id, task_id, task_name, created_time, status, progress,
                           total_accounts, completed_accounts, remark
                    FROM invite_tasks 
                    ORDER BY created_time DESC
                ''')
                
                columns = [description[0] for description in cursor.description]
                tasks = []
                
                for row in cursor.fetchall():
                    task = dict(zip(columns, row))
                    tasks.append(task)
                
                return tasks
                
        except Exception as e:
            print(f"获取邀请任务失败: {e}")
            return []
    
    def get_invite_details(self, limit: int = 100) -> List[Dict[str, Any]]:
        """获取邀请明细列表
        
        Args:
            limit: 限制返回的记录数
            
        Returns:
            List[Dict]: 邀请明细列表
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT invite_time, user_name, group_name, invite_url, inviter_name, invite_result, 
                           remark, status, task_id, join_time
                    FROM invite_details 
                    ORDER BY invite_time DESC
                    LIMIT ?
                ''', (limit,))
                
                columns = [description[0] for description in cursor.description]
                details = []
                
                for row in cursor.fetchall():
                    detail = dict(zip(columns, row))
                    details.append(detail)
                
                return details
                
        except Exception as e:
            print(f"获取邀请明细失败: {e}")
            return []
    
    def create_invite_task(self, task_name: str, total_accounts: int = 0) -> Optional[str]:
        """创建邀请任务
        
        Args:
            task_name: 任务名称
            total_accounts: 总账号数
            
        Returns:
            Optional[str]: 任务ID，失败返回None
        """
        try:
            import uuid
            task_id = f"TASK{uuid.uuid4().hex[:8].upper()}"
            
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    INSERT INTO invite_tasks 
                    (task_id, task_name, total_accounts, status, progress)
                    VALUES (?, ?, ?, '进行中', 0)
                ''', (task_id, task_name, total_accounts))
                
                conn.commit()
                return task_id
                
        except Exception as e:
            print(f"创建邀请任务失败: {e}")
            return None
    
    def add_invite_detail(self, user_name: str, group_name: str, task_id: str = None, 
                         invite_result: str = "等待中", remark: str = "", invite_url: str = "", 
                         inviter_name: str = "", join_time: str = None) -> bool:
        """添加邀请明细
        
        Args:
            user_name: 用户名
            group_name: 群名称
            task_id: 任务ID
            invite_result: 邀请结果
            remark: 备注
            invite_url: 邀请链接
            inviter_name: 邀请人昵称
            join_time: 进群时间
            
        Returns:
            bool: 操作是否成功
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    INSERT INTO invite_details 
                    (user_name, group_name, invite_url, inviter_name, task_id, invite_result, remark, status, join_time)
                    VALUES (?, ?, ?, ?, ?, ?, ?, '未处理', ?)
                ''', (user_name, group_name, invite_url, inviter_name, task_id, invite_result, remark, join_time))
                
                conn.commit()
                return True
                
        except Exception as e:
            print(f"添加邀请明细失败: {e}")
            return False

    def update_invite_detail(self, user_name: str, group_name: str, invite_result: str, 
                           remark: str = "") -> bool:
        """更新邀请明细结果
        
        Args:
            user_name: 用户名
            group_name: 群名称
            invite_result: 邀请结果
            remark: 备注
            
        Returns:
            bool: 操作是否成功
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                # 直接使用复合主键更新
                cursor.execute('''
                    UPDATE invite_details 
                    SET invite_result = ?, remark = ?, status = '已处理'
                    WHERE user_name = ? AND group_name = ?
                ''', (invite_result, remark, user_name, group_name))
                conn.commit()
                return cursor.rowcount > 0
        except Exception as e:
            print(f"更新邀请明细失败: {e}")
            return False

    def update_invite_detail_by_url(self, user_name: str, invite_url: str, invite_result: str, 
                                   remark: str = "") -> bool:
        """通过邀请链接更新邀请明细结果
        
        Args:
            user_name: 用户名
            invite_url: 邀请链接
            invite_result: 邀请结果
            remark: 备注
            
        Returns:
            bool: 操作是否成功
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                # 直接使用复合主键更新
                cursor.execute('''
                    UPDATE invite_details 
                    SET invite_result = ?, remark = ?, status = '已处理'
                    WHERE user_name = ? AND invite_url = ?
                ''', (invite_result, remark, user_name, invite_url))
                conn.commit()
                return cursor.rowcount > 0
        except Exception as e:
            print(f"通过邀请链接更新邀请明细失败: {e}")
            return False

    def get_account_invite_stats(self) -> Dict[str, Dict[str, int]]:
        """获取每个账号的进群统计信息
        包括没有邀请记录的账号（显示为0/0）
        
        Returns:
            Dict[str, Dict[str, int]]: 格式为 {user_name: {'total_invites': int, 'successful_consents': int}}
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                # 使用LEFT JOIN确保所有账号都显示，即使没有邀请记录
                cursor.execute('''
                    SELECT ua.user_name,
                           COALESCE(COUNT(id.user_name), 0) as total_invites,
                           COALESCE(SUM(CASE WHEN id.invite_result IN ('已同意', '同意', '成功') THEN 1 ELSE 0 END), 0) as successful_consents
                    FROM user_accounts ua
                    LEFT JOIN invite_details id ON ua.user_name = id.user_name
                    GROUP BY ua.user_name
                ''')
                
                stats = {}
                for row in cursor.fetchall():
                    user_name, total_invites, successful_consents = row
                    stats[user_name] = {
                        'total_invites': total_invites or 0,
                        'successful_consents': successful_consents or 0
                    }
                
                return stats
                
        except Exception as e:
            print(f"获取账号进群统计失败: {e}")
            return {}

    def get_account_consent_stats(self) -> Dict[str, Dict[str, int]]:
        """获取每个账号的同意进群操作统计信息（不论成功与失败）
        包括没有邀请记录的账号（显示为0/0）
        
        Returns:
            Dict[str, Dict[str, int]]: 格式为 {user_name: {'total_invites': int, 'consent_operations': int, 'successful_consents': int, 'failed_consents': int}}
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                # 使用LEFT JOIN确保所有账号都显示，即使没有邀请记录
                cursor.execute('''
                    SELECT ua.user_name,
                           COALESCE(COUNT(id.user_name), 0) as total_invites,
                           COALESCE(SUM(CASE WHEN id.invite_result IN ('已同意', '失败') THEN 1 ELSE 0 END), 0) as consent_operations,
                           COALESCE(SUM(CASE WHEN id.invite_result = '已同意' THEN 1 ELSE 0 END), 0) as successful_consents,
                           COALESCE(SUM(CASE WHEN id.invite_result = '失败' THEN 1 ELSE 0 END), 0) as failed_consents
                    FROM user_accounts ua
                    LEFT JOIN invite_details id ON ua.user_name = id.user_name
                    GROUP BY ua.user_name
                ''')
                
                stats = {}
                for row in cursor.fetchall():
                    user_name, total_invites, consent_operations, successful_consents, failed_consents = row
                    stats[user_name] = {
                        'total_invites': total_invites or 0,
                        'consent_operations': consent_operations or 0,
                        'successful_consents': successful_consents or 0,
                        'failed_consents': failed_consents or 0
                    }
                
                return stats
                
        except Exception as e:
            print(f"获取账号同意操作统计失败: {e}")
            return {}

    def clear_user_invite_data(self, user_name: str) -> bool:
        """清空指定用户的所有邀请记录
        
        Args:
            user_name: 用户名
            
        Returns:
            bool: 操作是否成功
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 删除该用户的所有邀请记录
                cursor.execute('''
                    DELETE FROM invite_details 
                    WHERE user_name = ?
                ''', (user_name,))
                
                deleted_count = cursor.rowcount
                conn.commit()
                
                print(f"已清空用户 {user_name} 的 {deleted_count} 条邀请记录")
                return True
                
        except Exception as e:
            print(f"清空用户邀请记录失败: {user_name}, 错误: {e}")
            return False

    def clear_all_invite_details(self) -> bool:
        """清空所有邀请明细记录"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('DELETE FROM invite_details')
                conn.commit()
                return True
        except Exception as e:
            print(f"清空邀请明细失败: {e}")
            return False

    def add_user_group(self, user_name: str, qid: str, group_name: str, inviter_name: str = None, remark: str = "") -> bool:
        """添加用户群聊记录
        
        Args:
            user_name: 用户名
            qid: 群聊ID
            group_name: 群名称
            inviter_name: 邀请人昵称
            remark: 备注
            
        Returns:
            bool: 操作是否成功
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 检查是否已存在相同的qid记录
                cursor.execute('''
                    SELECT id FROM user_groups 
                    WHERE user_name = ? AND qid = ?
                ''', (user_name, qid))
                
                if cursor.fetchone():
                    print(f"群聊记录已存在: {user_name} - {qid}")
                    return True
                
                # 插入新群聊记录
                cursor.execute('''
                    INSERT INTO user_groups 
                    (user_name, qid, group_name, inviter_name, remark)
                    VALUES (?, ?, ?, ?, ?)
                ''', (user_name, qid, group_name, inviter_name, remark))
                
                conn.commit()
                print(f"群聊记录添加成功: {user_name} - {group_name} (qid: {qid})")
                return True
                
        except Exception as e:
            print(f"添加群聊记录失败: {e}")
            return False
    
    def get_user_groups(self, user_name: str = None) -> List[Dict[str, Any]]:
        """获取用户群聊列表
        
        Args:
            user_name: 用户名，如果为None则获取所有用户的群聊
            
        Returns:
            List[Dict]: 群聊信息列表
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                if user_name:
                    cursor.execute('''
                        SELECT id, user_name, qid, group_name, inviter_name, 
                               invite_time, join_time, status, remark
                        FROM user_groups 
                        WHERE user_name = ?
                        ORDER BY join_time DESC
                    ''', (user_name,))
                else:
                    cursor.execute('''
                        SELECT id, user_name, qid, group_name, inviter_name, 
                               invite_time, join_time, status, remark
                        FROM user_groups 
                        ORDER BY join_time DESC
                    ''')
                
                columns = [description[0] for description in cursor.description]
                groups = []
                
                for row in cursor.fetchall():
                    group = dict(zip(columns, row))
                    groups.append(group)
                
                return groups
                
        except Exception as e:
            print(f"获取群聊列表失败: {e}")
            return []
    
    def get_group_by_qid(self, qid: str) -> Optional[Dict[str, Any]]:
        """根据qid获取群聊信息
        
        Args:
            qid: 群聊ID
            
        Returns:
            Optional[Dict]: 群聊信息，如果不存在返回None
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT id, user_name, qid, group_name, inviter_name, 
                           invite_time, join_time, status, remark
                    FROM user_groups 
                    WHERE qid = ?
                ''', (qid,))
                
                row = cursor.fetchone()
                if row:
                    columns = [description[0] for description in cursor.description]
                    return dict(zip(columns, row))
                return None
                
        except Exception as e:
            print(f"获取群聊信息失败: {e}")
            return None
    
    def update_group_status(self, qid: str, status: str, remark: str = "") -> bool:
        """更新群聊状态
        
        Args:
            qid: 群聊ID
            status: 状态 (active, left, removed)
            remark: 备注
            
        Returns:
            bool: 操作是否成功
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    UPDATE user_groups 
                    SET status = ?, remark = ?
                    WHERE qid = ?
                ''', (status, remark, qid))
                
                conn.commit()
                return cursor.rowcount > 0
                
        except Exception as e:
            print(f"更新群聊状态失败: {e}")
            return False
    
    def get_user_group_stats(self) -> Dict[str, Dict[str, int]]:
        """获取每个用户的群聊统计信息
        
        Returns:
            Dict[str, Dict[str, int]]: 格式为 {user_name: {'total_groups': int, 'active_groups': int}}
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT user_name,
                           COUNT(*) as total_groups,
                           SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_groups
                    FROM user_groups
                    GROUP BY user_name
                ''')
                
                stats = {}
                for row in cursor.fetchall():
                    user_name, total_groups, active_groups = row
                    stats[user_name] = {
                        'total_groups': total_groups or 0,
                        'active_groups': active_groups or 0
                    }
                
                return stats
                
        except Exception as e:
            print(f"获取用户群聊统计失败: {e}")
            return {}


    def add_template(self, template_data: Dict[str, Any]) -> bool:
        """添加模板
        
        Args:
            template_data: 模板数据字典，包含name, description, dialogues
            
        Returns:
            bool: 操作是否成功
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 插入模板基本信息
                cursor.execute('''
                    INSERT INTO templates (name, description, create_time, update_time)
                    VALUES (?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
                ''', (
                    template_data['name'],
                    template_data.get('description', '')
                ))
                
                template_id = cursor.lastrowid
                
                # 插入对话内容
                dialogues = template_data.get('dialogues', [])
                for i, dialogue in enumerate(dialogues):
                    # 处理content字段：如果启用分组，content为空字符串；否则使用实际内容
                    content = dialogue.get('content', '') if not dialogue.get('use_groups', False) else ''
                    
                    cursor.execute('''
                        INSERT INTO template_dialogues 
                        (template_id, speaker, message_type, content, use_groups, group_data, order_index, create_time)
                        VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
                    ''', (
                        template_id,
                        dialogue['speaker'],
                        dialogue['message_type'],
                        content,
                        dialogue.get('use_groups', False),
                        json.dumps(dialogue.get('group_data', {})) if dialogue.get('group_data') else None,
                        i
                    ))
                
                conn.commit()
                print(f"模板添加成功: {template_data['name']}")
                return True
                
        except Exception as e:
            print(f"添加模板失败: {e}")
            return False
    
    def get_all_templates(self) -> List[Dict[str, Any]]:
        """获取所有模板
        
        Returns:
            List[Dict[str, Any]]: 模板列表
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 获取模板基本信息
                cursor.execute('''
                    SELECT id, name, description, create_time, update_time
                    FROM templates
                    ORDER BY create_time DESC
                ''')
                
                templates = []
                for row in cursor.fetchall():
                    template = {
                        'id': row[0],
                        'name': row[1],
                        'description': row[2],
                        'create_time': row[3],
                        'update_time': row[4],
                        'dialogues': []
                    }
                    
                    # 获取该模板的所有对话
                    cursor.execute('''
                        SELECT speaker, message_type, content, use_groups, group_data, order_index
                        FROM template_dialogues
                        WHERE template_id = ?
                        ORDER BY order_index
                    ''', (template['id'],))
                    
                    for dialogue_row in cursor.fetchall():
                        dialogue = {
                            'speaker': dialogue_row[0],
                            'message_type': dialogue_row[1],
                            'content': dialogue_row[2],
                            'use_groups': bool(dialogue_row[3]),
                            'order_index': dialogue_row[5]
                        }
                        
                        # 解析分组数据
                        if dialogue_row[4]:
                            try:
                                dialogue['group_data'] = json.loads(dialogue_row[4])
                            except:
                                dialogue['group_data'] = {}
                        else:
                            dialogue['group_data'] = {}
                        
                        template['dialogues'].append(dialogue)
                    
                    templates.append(template)
                
                return templates
                
        except Exception as e:
            print(f"获取模板列表失败: {e}")
            return []
    
    def get_template_by_id(self, template_id: int) -> Optional[Dict[str, Any]]:
        """根据ID获取模板
        
        Args:
            template_id: 模板ID
            
        Returns:
            Optional[Dict[str, Any]]: 模板信息，如果不存在返回None
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 获取模板基本信息
                cursor.execute('''
                    SELECT id, name, description, create_time, update_time
                    FROM templates
                    WHERE id = ?
                ''', (template_id,))
                
                row = cursor.fetchone()
                if not row:
                    return None
                
                template = {
                    'id': row[0],
                    'name': row[1],
                    'description': row[2],
                    'create_time': row[3],
                    'update_time': row[4],
                    'dialogues': []
                }
                
                # 获取该模板的所有对话
                cursor.execute('''
                    SELECT speaker, message_type, content, use_groups, group_data, order_index
                    FROM template_dialogues
                    WHERE template_id = ?
                    ORDER BY order_index
                ''', (template_id,))
                
                for dialogue_row in cursor.fetchall():
                    dialogue = {
                        'speaker': dialogue_row[0],
                        'message_type': dialogue_row[1],
                        'content': dialogue_row[2],
                        'use_groups': bool(dialogue_row[3]),
                        'order_index': dialogue_row[5]
                    }
                    
                    # 解析分组数据
                    if dialogue_row[4]:
                        try:
                            dialogue['group_data'] = json.loads(dialogue_row[4])
                        except:
                            dialogue['group_data'] = {}
                    else:
                        dialogue['group_data'] = {}
                    
                    template['dialogues'].append(dialogue)
                
                return template
                
        except Exception as e:
            print(f"获取模板失败: {e}")
            return None
    
    def update_template(self, template_id: int, template_data: Dict[str, Any]) -> bool:
        """更新模板
        
        Args:
            template_id: 模板ID
            template_data: 模板数据字典
            
        Returns:
            bool: 操作是否成功
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 更新模板基本信息
                cursor.execute('''
                    UPDATE templates 
                    SET name = ?, description = ?, update_time = CURRENT_TIMESTAMP
                    WHERE id = ?
                ''', (
                    template_data['name'],
                    template_data.get('description', ''),
                    template_id
                ))
                
                # 删除原有对话
                cursor.execute('DELETE FROM template_dialogues WHERE template_id = ?', (template_id,))
                
                # 插入新对话
                dialogues = template_data.get('dialogues', [])
                for i, dialogue in enumerate(dialogues):
                    # 处理content字段：如果启用分组，content为空字符串；否则使用实际内容
                    content = dialogue.get('content', '') if not dialogue.get('use_groups', False) else ''
                    
                    cursor.execute('''
                        INSERT INTO template_dialogues 
                        (template_id, speaker, message_type, content, use_groups, group_data, order_index, create_time)
                        VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
                    ''', (
                        template_id,
                        dialogue['speaker'],
                        dialogue['message_type'],
                        content,
                        dialogue.get('use_groups', False),
                        json.dumps(dialogue.get('group_data', {})) if dialogue.get('group_data') else None,
                        i
                    ))
                
                conn.commit()
                print(f"模板更新成功: ID {template_id}")
                return True
                
        except Exception as e:
            print(f"更新模板失败: {e}")
            return False
    
    def delete_template(self, template_id: int) -> bool:
        """删除模板
        
        Args:
            template_id: 模板ID
            
        Returns:
            bool: 操作是否成功
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                cursor.execute(
                    "DELETE FROM templates WHERE id = ?",
                    (template_id,)
                )
                
                conn.commit()
                print(f"模板删除成功: ID {template_id}")
                return True
                
        except Exception as e:
            print(f"删除模板失败: {e}")
            return False

    def get_pending_invites(self) -> List[Dict[str, Any]]:
        """获取所有未完成的邀请任务
        
        Returns:
            List[Dict[str, Any]]: 未完成的邀请任务列表
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT user_name, group_name, invite_url, inviter_name, invite_time, remark
                    FROM invite_details
                    WHERE invite_result = '等待中' AND status = '未处理'
                    ORDER BY invite_time ASC
                ''')
                
                pending_invites = []
                for row in cursor.fetchall():
                    user_name, group_name, invite_url, inviter_name, invite_time, remark = row
                    pending_invites.append({
                        'user_name': user_name,
                        'group_name': group_name,
                        'invite_url': invite_url,
                        'inviter_name': inviter_name,
                        'invite_time': invite_time,
                        'remark': remark
                    })
                
                return pending_invites
                
        except Exception as e:
            print(f"获取未完成邀请任务失败: {e}")
            return []


# 创建全局数据库管理器实例
db_manager = DatabaseManager() 